

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_tb_svcshipment]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_tb_svcshipment]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.v_tb_svcshipment
AS
SELECT CAST(0 AS bit) AS choice, m.bcode AS m_bcode, m.bdate AS m_bdate, 
      m.remark AS m_remark, m.bkind AS m_bkind, m.bstate AS m_bstate, 
      m.amount AS m_Amount, 2030 AS btype, m.bid AS m_bid, m.deptid AS m_deptid, m.traderid as m_traderid, m.btraderid as m_btraderid,
      m.invamt as m_invamt, m.invclose as m_invclose,
      m.custom1 AS m_custom1, m.custom2 AS m_custom2, m.custom3 AS m_custom3, 
      m.custom4 AS m_custom4, m.custom5 AS m_custom5, m.custom6 AS m_custom6, 
      m.custom7 AS m_custom7, m.custom8 AS m_custom8, m.custom9 AS m_custom9, 
      m.custom10 AS m_custom10, m.custom11 AS m_custom11, 
      m.custom12 AS m_custom12, m.custom13 AS m_custom13, 
      m.custom14 AS m_custom14, m.custom15 AS m_custom15, 
      m.create_by AS m_create_by, m.filid AS m_filid, m.check_by AS m_check_by, 
  m.voucherid m_voucherid,m.notetype m_notetype,nt.kname m_notetypename,noteno m_noteno,m.balanceid m_balanceid,accountid m_accountid,
  m.moneyid m_moneyid,m.exchrate m_exchrate,mn.code as moneycode,mn.name as moneyname,
  m.dcamount m_dcamount,m.dcrelationamt m_dcrelationamt,m.dcbalanceamt m_dcbalanceamt,
  checkno m_checkno,m.transporttype m_transporttype,balancedate m_balancedate,termdays m_termdays,
  m.disc m_disc,relationamt m_relationamt,m.targetaddr m_targetaddr,m.contacter m_contacter,m.contactphone m_contactphone,m.contactfax m_contactfax,
  t.code m_t_code,t.[name] m_t_name,t.[name] m_tradername,t.provider m_provider,t.scamt m_scamt,
  bt.code m_bt_code,bt.[name] m_bt_name,
      cr.UI_UserName AS m_createuser, ck.UI_UserName AS m_checkuser, 
      f.FI_FilNM AS m_filname, 
d.*, p.code AS p_code, p.name AS p_name, 
      p.spec AS p_spec, p.name AS productname, p.calccost AS p_calccost, 
  '' as producttypename, p.producttypeid,  p.producttrademarkid,
  p.kind AS p_kind, u.unitname, bu.unitname AS baseunitname, u.barcode , dp.code as depotcode,dp.name as depotname,
  p.custom1 p_custom1, p.custom2 p_custom2, p.custom3 p_custom3, p.custom4 p_custom4, p.custom5 p_custom5 ,
  p.custom6 p_custom6, p.custom7 p_custom7, p.custom8 p_custom8, p.custom9 p_custom9, p.custom10 p_custom10, 
  p.custom11 p_custom11, p.custom12 p_custom12, p.custom13 p_custom13, p.custom14 p_custom14, p.custom15 p_custom15,
      au.unitname AS unitassname, 
  m.empid as m_empid,e.EM_Name AS empname
FROM dbo.tb_svcshipment m 
  INNER JOIN (select ROW_NUMBER() over (partition by bid order by bno) as bdtlno,* from dbo.tb_svcshipmentdtl) d ON m.bid = d.bid 
  LEFT OUTER JOIN dbo.ts_usersinfo cr ON m.create_by = cr.UI_UserID 
  LEFT OUTER JOIN dbo.ts_usersinfo ck ON m.check_by = ck.UI_UserID 
  LEFT OUTER JOIN dbo.td_filiale f ON m.filid = f.FI_FilID 
  LEFT OUTER JOIN dbo.td_product p ON d.productid = p.p_id 
  LEFT OUTER JOIN dbo.td_depot dp ON d.depotid = dp.depotid
  LEFT OUTER JOIN dbo.td_unitprice u ON d.unitid = u.unitid 
  LEFT OUTER JOIN dbo.td_unitprice au ON d.productid = au.productid AND au.u_no=0 
  LEFT OUTER JOIN dbo.td_unitprice bu ON d.productid = bu.productid AND bu.baseunit=1 
  LEFT OUTER JOIN dbo.td_empmaster e ON m.empid = e.EM_EmployeeID 
  left join td_trader t on m.traderid=t.traderid
  left join td_trader bt on m.btraderid=bt.traderid
  left join td_money mn on m.moneyid=mn.moneyid
  left join ts_kind nt on m.notetype=nt.kid and nt.kind=5014


--select * FROM dbo.tb_svcshipment m 
--  INNER JOIN dbo.tb_svcshipmentdtl d ON m.bid = d.bid 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

